﻿using System.Collections.ObjectModel;
namespace Helper
{
    public static class DatabaseScript
    {
        static string dbName = "";
        #region Fields

        static string CreateScript
        {
            get;
            set;
        }
        static string DatabaseName
        {
            get
            {
                if (string.IsNullOrWhiteSpace(dbName))
                    dbName = Helper.Properties.Settings.Default.DBName;
                return dbName;
            }
            set { dbName = value; }
        }
        static string DbCreateScript
        {
            get;
            set;
        }
        static string SchemasCreateScript
        {
            get;
            set;
        }
        static string TablesCreateScript
        {
            get;
            set;
        }
        static string ObjectsCreateScript
        {
            get;
            set;
        }
        static string DropScript
        {
            get;
            set;
        }

        #endregion

        #region Methods


        internal static string GetTablesAlterScript(TableDefn oldT, TableDefn newT)
        {
            ObservableCollection<ColumnDefn> commonColumns = GetCommonColumns(oldT, newT);
            string alterStr = "USE " + Helper.Properties.Settings.Default.DBName + "\r\n" +
                "BEGIN TRANSACTION\r\n" +
               "SET QUOTED_IDENTIFIER ON\r\n" +
               "SET ARITHABORT ON\r\n" +
               "SET NUMERIC_ROUNDABORT OFF\r\n" +
               "SET CONCAT_NULL_YIELDS_NULL ON\r\n" +
               "SET ANSI_NULLS ON\r\n" +
               "SET ANSI_PADDING ON\r\n" +
               "SET ANSI_WARNINGS ON\r\n" +
               "COMMIT\r\n" +
               "BEGIN TRANSACTION\r\n";

            foreach (ConstraintDefn v in oldT.ConstraintCollection)
                alterStr += "ALTER TABLE " + newT.Schema + "." + newT.NameOfTable + "\r\n" +
                    "DROP CONSTRAINT " + v.Name + "\r\n";

            alterStr += "CREATE TABLE " + newT.Schema + ".Tmp_" + newT.NameOfTable + "\r\n" +
    "(\r\n";
            foreach (ColumnDefn col in newT.ColumnCollection)
            {
                if (newT.PrimaryKeyField.ToUpperInvariant() == col.ColumnName.ToUpperInvariant())
                    alterStr += col.ColumnName + " " + col.FullDataTypeName + " NOT NULL,";
                else
                    alterStr += col.ColumnName + " " + col.FullDataTypeName + ",";
            }
            alterStr = alterStr.Remove(alterStr.Length - 1);
            alterStr += ")  ON [PRIMARY]\r\n" +
        "ALTER TABLE " + newT.Schema + ".Tmp_" + newT.NameOfTable + " SET (LOCK_ESCALATION = TABLE)\r\n";

            foreach (ConstraintDefn con in newT.ConstraintCollection)
                alterStr += "ALTER TABLE " + newT.Schema + ".Tmp_" + newT.NameOfTable + " ADD CONSTRAINT\r\n" +
            con.Name + " DEFAULT " + con.Definition + " FOR " + con.ColumnName + "\r\n";
            alterStr +=
        "IF EXISTS(SELECT * FROM " + oldT.Schema + "." + oldT.NameOfTable + ")\r\n" +
             "EXEC('INSERT INTO " + newT.Schema + ".Tmp_" + newT.NameOfTable + " (";

            foreach (ColumnDefn col in commonColumns)
                alterStr += col.ColumnName + ",";
            alterStr = alterStr.Remove(alterStr.Length - 1);
            alterStr += ")\r\nSELECT ";
            foreach (ColumnDefn col in commonColumns)
                alterStr += col.ColumnName + ",";
            alterStr = alterStr.Remove(alterStr.Length - 1);
            alterStr += " FROM " + newT.Schema + "." + newT.NameOfTable + " WITH (HOLDLOCK TABLOCKX)')\r\n" +

           "DROP TABLE " + newT.Schema + "." + newT.NameOfTable + "\r\n" +

           "EXECUTE sp_rename N'" + newT.Schema + ".Tmp_" + newT.NameOfTable + "', N'" + newT.NameOfTable + "', 'OBJECT' \r\n" +

       "ALTER TABLE " + newT.Schema + "." + newT.NameOfTable + " ADD CONSTRAINT\r\n" +
    "PK_" + newT.NameOfTable + " PRIMARY KEY CLUSTERED \r\n" +
    "(\r\n" +
    newT.PrimaryKeyField + "\r\n" +
    ") WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON," +
    " ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]\r\nCOMMIT";

            return alterStr;

        }

        private static ObservableCollection<ColumnDefn> GetCommonColumns(TableDefn oldT, TableDefn newT)
        {
            ObservableCollection<ColumnDefn> tempColl = new ObservableCollection<ColumnDefn>();
            foreach (ColumnDefn colDef in oldT.ColumnCollection)
            {
                foreach (ColumnDefn c2 in newT.ColumnCollection)
                    if (colDef.ColumnName.ToUpperInvariant() == c2.ColumnName.ToUpperInvariant())
                    {
                        tempColl.Add(colDef);
                        break;
                    }
            }
            return tempColl;
        }

        public static string GetScript(DatabaseDefn defn)
        {
            DatabaseName = defn.NameOfDatabase;
            SchemasCreateScript = GetSchemasScript(defn.SchemasDefinition);
            TablesCreateScript = GetTablesScript(defn.TablesDefinition);
            ObjectsCreateScript = GetObjectsScript(defn.ObjectsDefinition);
            DbCreateScript = GetDBCreateScript(defn.NameOfDatabase);

            CreateScript = DbCreateScript + SchemasCreateScript + TablesCreateScript + ObjectsCreateScript;
            return CreateScript;

        }
        public static string GetDefaultScript()
        {
            string d = GetScript(DatabaseDefn.GetDefaultDBDefn());
            return d;
        }
        
        public static string GetCurrentScript()
        {
            string d = GetScript(DatabaseDefn.GetCurrentDBDefn());
            return d;
        }

        internal static string GetObjectsScript(ObservableCollection<ObjectDefn> objCollection)
        {
            string MainDb = "USE " + DatabaseName + "\r\n";
            foreach (ObjectDefn t in objCollection)
            {
                MainDb += t.CreateScript + "\r\n";
            }
            return MainDb;
        }
        internal static string GetSchemasScript(ObservableCollection<string> schemaCollection)
        {
            string MainDb = "USE " + DatabaseName + "\r\n";
            foreach (string s in schemaCollection)
            {
                MainDb += "EXEC " + Helper.Properties.Settings.Default.DBName +
                "..sp_executesql N'CREATE SCHEMA [" + s + "];'\r\n";
            }

            return MainDb;
        }
        internal static string GetTablesScript(ObservableCollection<TableDefn> tableCollection)
        {
            string table = "USE " + DatabaseName + "\r\n";
            foreach (TableDefn t in tableCollection)
            {
                table += "CREATE TABLE [" + t.Schema + "].[" + t.NameOfTable + "](\r\n";
                foreach (ColumnDefn c in t.ColumnCollection)
                {
                    if ((c.DataType.ToUpperInvariant() == "VARBINARY") || (c.DataType.ToUpperInvariant() == "VARCHAR"))
                        table += "[" + c.ColumnName + "] [" + c.DataType + "] (" + c.MaxLength + ") " + c.IsNullable + ",\r\n";
                    else
                        table += "[" + c.ColumnName + "] [" + c.DataType + "] " + c.IsNullable + ",\r\n";
                }
                table += "CONSTRAINT [PK_" + t.NameOfTable + "] PRIMARY KEY CLUSTERED\r\n(\r\n" +
       "[" + t.PrimaryKeyField + "] ASC\r\n" +
   ")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]" +
   ") ON [PRIMARY]\r\n";
                foreach (ColumnDefn cd in t.ColumnCollection)
                {
                    if (cd.ColumnName.ToUpperInvariant() == "MODIFIEDDATE")
                        table += "ALTER TABLE [" + t.Schema + "].[" + t.NameOfTable + "] ADD  CONSTRAINT [DF_" + t.NameOfTable +
                            "_ModifiedDate]  DEFAULT (sysdatetime()) FOR [ModifiedDate]\r\n";
                    if (cd.ColumnName.ToUpperInvariant() == "ROWGUID")
                        table += "ALTER TABLE [" + t.Schema + "].[" + t.NameOfTable + "] ADD  CONSTRAINT [DF_" + t.NameOfTable +
                            "_rowguid]  DEFAULT (newid()) FOR [rowguid]\r\n";
                }
            }
            return table;
        }
        public static string GetDropScript()
        {
            return GetDropScript(Helper.Properties.Settings.Default.DBName);
        }
        public static string GetDropScript(string dbName)
        {
            string DropMainDb = "EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = '" +
                dbName + "'\r\n" +
                "USE master\r\n" +
                "ALTER DATABASE " + dbName +
                " SET SINGLE_USER WITH ROLLBACK IMMEDIATE\r\n" +
                "USE master\r\n" +
                "DROP DATABASE " + dbName + "";
            return DropMainDb;
        }
        #endregion



        internal static string GetDBCreateScript(string database)
        {
            return "USE Master\r\n" +
        "CREATE DATABASE [" + database + "] ON PRIMARY" +
        "( NAME = N'" + database + "', FILENAME = N'" + Helper.Properties.Settings.Default.DBFilePath + "'," +
        "SIZE = 4096KB , FILEGROWTH = 1024KB )\r\n" +
        "LOG ON" +
        "( NAME = N'" + database + "_log', FILENAME = N'" + Helper.Properties.Settings.Default.DBLogFilePath + "'," +
        "SIZE = 2048KB , FILEGROWTH = 10%)\r\n ALTER DATABASE [" + database + "] SET COMPATIBILITY_LEVEL = 80\r\n" +
        "ALTER DATABASE [" + database + "] SET AUTO_CLOSE ON\r\n";
        }
    }

}
